Google Optimizeのデータ
Google Optimizeのデータ
Analytics-Bigquery:
Google Analytisのreportで出る数字をBigQueryでも出す。
experiment Session
code:exp_sessions.sql
with a as(
SELECT
array(select as struct g.experimentId as exid, g.experimentVariant as exvr from unnest(h.experiment) as g
where g.experimentId = '-B6UV3XfR9m2t6f9ouKVBw')OFFSET(0) as exp ,CONCAT(fullVisitorId, SAFE_CAST(visitNumber AS string)) as sid
FROM xxx.ga_sessions_20200905 as tbl
,unnest(hits) as h
where exists(
select as struct g.experimentId, g.experimentVariant from unnest(h.experiment) as g
where g.experimentId = '-B6xxxx'
)
)
select exp.exid, exp.exvr, count(distinct sid) from a group by exp.exid, exp.exvr
conversion情報などは適宜つける。
他の情報をつかたもの。
code: summary.sql
with a as(
SELECT
device.deviceCategory
,visitorid, fullVisitorId,
array(select as struct g.experimentId, g.experimentVariant from unnest(h.experiment) as g)
,h.transaction.transactionRevenue
FROM xxxxx as tbl
,unnest(hits) as h
where h.experiment is not null and h.transaction.transactionRevenue > 100)
# experimentの値が、購入時にも渡されるかどうかは不明,,,
and exists(select as struct g.experimentId, g.experimentVariant from unnest(h.experiment) as g)
#group by hits.experiment.experimentid) select
experimentId, experimentVariant
,deviceCategory
,count(distinct fullVisitorId) as users
,sum(transactionRevenue) as revenue
from a
where experimentId is not null
group by experimentId, experimentVariant,deviceCategory
subqueryで、(select as struct ....) の部分、２つの項目を(g.experimentId, g.experimentVariant)を出してるので
そのままだと、
Scalar subquery produced more than one element となり、エラーになる。
array()で囲むとok.(google optimizeのexperimentは複数走ってるかもしれないので、配列ということを忘れない)
スカラー サブクエリ内の SELECT リストのフィールド数は、正確に 1 つである必要があります
ただ、select as struct...にしてるので、array型のsubqueryになっていて然るべきなのだが、、